﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Diagnostics;

using Microsoft.SqlServer.Management.Trace;
using Microsoft.SqlServer.Management.Common;
using System.Data.SqlClient;
using System.IO;
using System.Threading;
using System.Xml;
using System.Net;
using Microsoft.Win32;
using System.Security.Permissions;

namespace ASTraceAnalyzer
{
    public class ASTraceAnalyzer
    {
        int DurationOrdinal = -1;
        int EventClassOrdinal = -1;
        int TextDataOrdinal = -1;
        int SPIDOrdinal = -1;
        int ParamsOrdinal = -1;
        int CurrentTimeOrdinal = -1;
        int StartTimeOrdinal = -1;
        int ConnectionIDOrdinal = -1;

        public int iRecordCount = 0;
        public bool bAbortFlag = false;
        public bool bMultipleFiles = false;
        public string trc = "";
        Thread th;
        public List<Query> LongestXQueries = new List<Query>();
        public List<Query> QueriesWithoutEndTiming = new List<Query>();
        public delegate void ASTraceUpdateDelegate();
        public delegate void ASTraceSetTextDelegate(string text);

        bool CheckExistsInLongestX(TraceFile t, Int64 CurDuration, string file, int iRow, string parms)
        {
            Query alreadyExists = LongestXQueries.Find(q => q.Text.Trim() == t.GetString(TextDataOrdinal).Trim());
            if (alreadyExists != null)
            {
                if (alreadyExists.Duration <= CurDuration)
                {
                    alreadyExists.Duration = CurDuration;
                    alreadyExists.File = file;
                    alreadyExists.SPID = (int)t.GetValue(SPIDOrdinal);
                    alreadyExists.ConnectionID = (int)t.GetValue(ConnectionIDOrdinal);
                    alreadyExists.StartRow = iRow;
                    alreadyExists.Parameters = parms;
                }
                return true;
            }
            else
                return false;
        }

        void UpdateLongestX(TraceFile t, Int64 CurDuration, string file, int iRow, string parms)
        {
            foreach (Query q in LongestXQueries)
            {
                if (q.Duration <= CurDuration)
                {
                    q.Duration = CurDuration;
                    q.SPID = (int)t.GetValue(SPIDOrdinal);
                    q.Text = t.GetString(TextDataOrdinal);
                    q.File = file;
                    q.StartRow = iRow;
                    q.ConnectionID = (int)t.GetValue(ConnectionIDOrdinal);
                    q.Parameters = parms;
                    break;
                }
            }
        }

        public void ProcessTrace(string trace, bool bRollover = false, int AnalyzeLongestXQueries = 10, bool bDistinct = true, ASTraceSetTextDelegate SetTextDelegate = null, ASTraceUpdateDelegate UpdateDelegate = null)
        {
            bAbortFlag = false;
            iRecordCount = 0;
            Int64 CurDuration = 0;
            List<string> files = new List<string>();

            if (bRollover)
            {
                trc = trace.Substring(trace.LastIndexOf('\\') + 1);
                // Remove numbers trailing from trace file name (if they are there) as well as file extension...
                trc = trc.Substring(0, trc.Length - 4); int j = trc.Length - 1; while (Char.IsDigit(trc[j])) j--; trc = trc.Substring(0, j + 1);
                if (SetTextDelegate != null) SetTextDelegate("SSAS Long Running Query Performance Helper - " + trc + "*.trc");
            }
            else
            {
                if (SetTextDelegate != null) SetTextDelegate("SSAS Long Running Query Performance Helper - " + trace.Substring(trace.LastIndexOf('\\') + 1));
                trc = trace;
            }           

            if (bRollover)
            {
                string path = trace.Substring(0, trace.LastIndexOf('\\'));
                var fileInfos = Directory.GetFiles(path, trc + "*.trc", SearchOption.TopDirectoryOnly).Select(p => new FileInfo(p))
                    .OrderBy(x => x.CreationTime).ToList();
                files = fileInfos.Select(x => x.FullName).ToList();
            }
            else files.Add(trace);

            if (files.Count > 1) bMultipleFiles = true; else bMultipleFiles = false;

            LongestXQueries.Clear();
            QueriesWithoutEndTiming.Clear();

            th = new Thread(() =>
            {
                DateTime lastTimeInTrace = new DateTime();
                foreach (string f in files)
                {
                    if (bAbortFlag) break;
                    trc = f;
                    int iRow = 0;
                    TraceFile t = new TraceFile();

                    try
                    {
                        t.InitializeAsReader(f);
                        DurationOrdinal = t.GetOrdinal("Duration"); // line 129
                        EventClassOrdinal = t.GetOrdinal("EventClass"); // 130
                        TextDataOrdinal = t.GetOrdinal("TextData"); // 131
                        SPIDOrdinal = t.GetOrdinal("SPID"); // 132
                        ParamsOrdinal = t.GetOrdinal("RequestParameters"); //133
                        CurrentTimeOrdinal = t.GetOrdinal("CurrentTime"); // 134
                        StartTimeOrdinal = t.GetOrdinal("StartTime"); // 135
                        ConnectionIDOrdinal = t.GetOrdinal("ConnectionID"); // 136
                        
                        while (t.Read() && !bAbortFlag)
                        {
                            if (t.GetValue(CurrentTimeOrdinal) != null) lastTimeInTrace = t.GetDateTime(CurrentTimeOrdinal);
                            iRecordCount++;
                            iRow++;
                            string EventClass = (string)t.GetValue(EventClassOrdinal);
                            if (EventClass == "Query Begin")
                            {
                                QueriesWithoutEndTiming.Add(new Query { SPID = (int)t.GetValue(SPIDOrdinal), Text = (string)t.GetValue(TextDataOrdinal), Duration = null, End = null, Start = t.GetDateTime(StartTimeOrdinal), File = f, StartRow = iRow, Parameters = (string)t.GetValue(ParamsOrdinal), ConnectionID = (int)t.GetValue(ConnectionIDOrdinal) });
                            }
                            if (EventClass == "Query Subcube")
                            {
                                Query qryStart = QueriesWithoutEndTiming.Find(q => q.SPID == (int)t.GetValue(SPIDOrdinal) && q.ConnectionID == (int)t.GetValue(ConnectionIDOrdinal) && q.EndRow == "Unknown" );
                                if (qryStart != null)
                                {
                                    if (t.IsNull(DurationOrdinal)) CurDuration = 0; else CurDuration = (Int64)t.GetValue(DurationOrdinal);
                                    qryStart.SEDuration += CurDuration;
                                }
                            }
                            if (EventClass == "Query End")
                            {
                                if (t.IsNull(DurationOrdinal)) CurDuration = 0; else CurDuration = (Int64)t.GetValue(DurationOrdinal);
                                Query qryEnd = new Query { SPID = (int)t.GetValue(SPIDOrdinal), Text = t.GetString(TextDataOrdinal), Duration = CurDuration, End = t.GetDateTime(CurrentTimeOrdinal), Start = t.GetDateTime(StartTimeOrdinal), File = f, StartRow = -1, Parameters = "Query Begin event not found to supply parameters for this query.", ConnectionID = (int)t.GetValue(ConnectionIDOrdinal) };
                                Query qryStart = QueriesWithoutEndTiming.Find(q => q.SPID == (int)t.GetValue(SPIDOrdinal) && q.ConnectionID == (int)t.GetValue(ConnectionIDOrdinal) && q.Text.Trim() == t.GetString(TextDataOrdinal).Trim());
                                if (qryStart != null)
                                {
                                    qryEnd.Parameters = qryStart.Parameters;
                                    qryEnd.SEDuration = qryStart.SEDuration;
                                    qryEnd.EndRow = iRow;
                                    QueriesWithoutEndTiming.Remove(qryStart);
                                }
                                if (LongestXQueries.Count < AnalyzeLongestXQueries)
                                    if (bDistinct)
                                    {
                                        if (!CheckExistsInLongestX(t, CurDuration, f, iRow, qryEnd.Parameters))
                                            LongestXQueries.Add(qryEnd);
                                    }
                                    else LongestXQueries.Add(qryEnd);
                                else
                                    if (bDistinct)
                                    {
                                        if (!CheckExistsInLongestX(t, CurDuration, f, iRow, qryEnd.Parameters))
                                            UpdateLongestX(t, CurDuration, f, iRow, qryEnd.Parameters);
                                    }
                                    else UpdateLongestX(t, CurDuration, f, iRow, qryEnd.Parameters);
                            }
                        }
                    }

                    catch (Exception ex)
                    {
                        if (ex.Message == "Failed to find column ordinal.")
                        {
                            string MissingColumn = "";
                            /// THESE MUST BE CHANGED IF THE COUNT OF LINES PRIOR TO THEM ARE INCREASED OR DECREASED IN THIS FILE
                            /// THIS IS A VERY UGLY HACK BUT IT WORKS JUST FINE
                            if (ex.StackTrace.Contains("frmMain.cs:line 129")) MissingColumn = "Duration";
                            if (ex.StackTrace.Contains("frmMain.cs:line 130")) MissingColumn = "EventClass";
                            if (ex.StackTrace.Contains("frmMain.cs:line 131")) MissingColumn = "Text Data";
                            if (ex.StackTrace.Contains("frmMain.cs:line 132")) MissingColumn = "SPID";
                            if (ex.StackTrace.Contains("frmMain.cs:line 133")) MissingColumn = "Request Parameters";
                            if (ex.StackTrace.Contains("frmMain.cs:line 134")) MissingColumn = "Current Time";
                            if (ex.StackTrace.Contains("frmMain.cs:line 135")) MissingColumn = "Start Time";
                            if (ex.StackTrace.Contains("frmMain.cs:line 136")) MissingColumn = "ConnectionID";

                            throw new ASTraceAnalyzerException("This trace is missing a column required for analysis:\r\n" + MissingColumn + "\r\n\r\n" + f);
                        }
                        else throw new ASTraceAnalyzerException("Error occurred, possibly indicating corruption in tracefile.\r\nProcessing will attempt to continue.\r\n\r\n" + ex.ToString());
                    }
                    finally
                    {
                        t.Close();
                        t.Dispose();
                    }
                }
                foreach (Query q in QueriesWithoutEndTiming)
                {
                    if (q != null)
                    {
                        q.Duration = (Int64)lastTimeInTrace.Subtract(q.Start.Value).TotalMilliseconds;
                    }
                }
                if (UpdateDelegate != null ) 
                    UpdateDelegate.Invoke();
            });
            th.Start();
            if (UpdateDelegate == null)
                th.Join();
        }
    }

    public class Query
    {
        public Query() { }
        public int SPID;
        public string Text;
        public Int64? Duration = 0;
        public Int64? SEDuration = 0;
        public Int64? FEDuration
        {
            get { return Duration - SEDuration; }
        }

        public string File;
        public string Parameters;
        public DateTime? Start;
        public DateTime? End;
        public int ConnectionID;

        private int startRow = -1;
        public object StartRow
        {
            get { if (startRow == -1) return "Unknown"; else return startRow; }
            set { startRow = (value is int) ? ((int)value) : -1 ; }
        }
        private int endRow = -1;
        public object EndRow
        {
            get { if (endRow == -1) return "Unknown"; else return endRow; }
            set { endRow = (value is int) ? ((int)value) : -1; }
        }
    }

    public class ASTraceAnalyzerException : Exception
    {
        public ASTraceAnalyzerException(string message) : base(message) { }
    }
}
